#!/usr/bin/env python
# -*- coding: utf-8 -*-

import pymysql


def query_table_structure():
    output_file = 'query_result.txt'
    try:
        with open(output_file, 'w', encoding='utf-8') as f:
            f.write("正在连接数据库...\n")
            print("正在连接数据库...")

            conn = pymysql.connect(
                host="rm-2zer2f33rws29ogb1do.mysql.rds.aliyuncs.com",
                port=3306,
                user="zxy",
                password="zxy@800901",
                database="ruoyi"
            )
            f.write("数据库连接成功！\n")
            print("数据库连接成功！")
            cursor = conn.cursor()

            # 先检查表是否存在
            f.write("检查表是否存在...\n")
            print("检查表是否存在...")
            cursor.execute("SHOW TABLES LIKE 'dz_real_sales_bottomtable'")
            table_exists = cursor.fetchone()

            if not table_exists:
                f.write("表 dz_real_sales_bottomtable 不存在！\n")
                f.write("查看所有包含 'real_sales' 的表：\n")
                print("表 dz_real_sales_bottomtable 不存在！")
                print("查看所有包含 'real_sales' 的表：")
                cursor.execute("SHOW TABLES LIKE '%real_sales%'")
                similar_tables = cursor.fetchall()
                for table in similar_tables:
                    f.write(f"  - {table[0]}\n")
                    print(f"  - {table[0]}")
                cursor.close()
                conn.close()
                return

            f.write("=== dz_real_sales_bottomtable 表结构 ===\n")
            print("=== dz_real_sales_bottomtable 表结构 ===")
            cursor.execute('DESCRIBE dz_real_sales_bottomtable')
            columns = cursor.fetchall()

            f.write("字段名\t\t类型\t\t\t是否为空\t键\t默认值\t\t额外\n")
            f.write("-" * 80 + "\n")
            print("字段名\t\t类型\t\t\t是否为空\t键\t默认值\t\t额外")
            print("-" * 80)

            for col in columns:
                field, type_, null, key, default, extra = col
                default_str = str(default) if default is not None else 'NULL'
                line = f"{field:<20} {type_:<20} {null:<10} {key:<5} {default_str:<15} {extra:<15}"
                f.write(line + "\n")
                print(line)

            f.write("\n=== 建表语句 ===\n")
            print("\n=== 建表语句 ===")
            cursor.execute('SHOW CREATE TABLE dz_real_sales_bottomtable')
            create_sql = cursor.fetchone()
            if create_sql:
                f.write(create_sql[1] + "\n")
                print(create_sql[1])

            cursor.close()
            conn.close()

    except Exception as e:
        error_msg = f"查询表结构时出错: {e}"
        with open(output_file, 'a', encoding='utf-8') as f:
            f.write(error_msg + "\n")
            import traceback
            f.write(traceback.format_exc())
        print(error_msg)
        import traceback
        traceback.print_exc()


if __name__ == "__main__":
    query_table_structure()
